package kines.ct;

import kines.SerializableDB;
import javax.servlet.http.HttpSession;
import java.sql.*;
import java.util.*;

/**
 * <p>Title: CT</p>
 * <p>Description: Intranet Consorcio Terrenos</p>
 * <p>Copyright: Copyright (c) 2003</p>
 * <p>Company: Consorcio Terrenos</p>
 * @author Jose Delgado
 * @version 1.0
 */

public class SalesPlan implements SerializableDB{
  /**
   * Management Cost
   */
  public double managementCost;
  public void setManagementCost( double managementCost ){
    this.managementCost = managementCost;
  }
  public double getManagementCost( ){
    return managementCost;
  }
  /**
   * Development Code
   */
  public String developmentCode;
  protected void setDevelopmentCode( String developmentCode ){
    this.developmentCode = developmentCode;
  }
  public Development getDevelopmentCode( )throws Exception{
    return Development.getData( developmentCode );
  }
  public String getDevelopmentCodeStr( ){
    return developmentCode;
  }
  /**
   * Number Plan
   */
  public int number;
  protected void setNumber( int number ){
    this.number = number;
  }
  public int getNumber( ){
    return number;
  }
  /**
   * Term
   */
  public int term;
  public void setTerm( int term ){
    this.term = term;
  }
  public int getTerm( ){
    return term;
  }
  /**
   * Down payment
   */
  public double downPayment;
  public void setDownPayment( double downPayment ){
    this.downPayment = downPayment;
  }
  public double getDownPayment( ){
    return downPayment;
  }
  /**
   * Deduct in Cash payment
   */
  public double cashPayment;
  public void setCashPayment( double cashPayment ){
    this.cashPayment = cashPayment;
  }
  public double getCashPayment( ){
    return cashPayment;
  }
  /**
   * Monthly Interest
   */
  public double monthlyInterest;
  public void setMonthlyInterest(double monthlyInterest ) {
    this.monthlyInterest = monthlyInterest;
  }
  public double getMonthlyInterest() {
    return monthlyInterest;
  }
  /**
   * Insurance
   */
  public double insurance;
  public void setInsurance(double insurance ) {
    this.insurance = insurance;
  }
  public double getInsurance() {
    return insurance;
  }
  /**
   * Factor
   */
  public double factor;
  public void setFactor(double factor ) {
    this.factor = factor;
  }
  public double getFactor() {
    return factor;
  }
  /**
   * Gets Description of Anual Interest
   * @return Description of Anual Interest
   */
  public String getAnualInterest( ){
    if( term == 0 ){
      return "";
    }else{
      if( monthlyInterest == 0 ){
        return "SIN INTERESES";
      }else{
        return ""+(int)(monthlyInterest*12)+"% INT ANUAL";
      }
    }
  }
  /**
   * Short Description of this plan
   * @return
   */
  public String getShortDescription( ){
    if( term == 0 ){
      return "CONTADO";
    }else{
      return  ""+(int)downPayment+"% ENG";
    }
  }
  /**
   * Gets Description of the DownPayment
   * @return Description of DownPayment
   */
  public String getDescriptionDownPayment( ){
    if( term == 0 ){
      if( cashPayment == 0 ){
        return "";
      }else{
        return "" + (int)cashPayment + "% de Descuento";
      }
    }else{
      return  ""+(int)downPayment+"% de Enganche";
    }
  }
  /**
  * Gets Description of the Term of this plan
  * @return Description of Term
  */
  public String getDescriptionTerm( ){
    if( term == 0 ){
      return "Contado";
    }else{
      return  "Saldo a "+term+" meses";
    }
  }
  /**
  * Gets Description of the Interest of this plan
  * @return Description of Interest
  */
  public String getDescriptionInterest( ){
    if( term == 0 && monthlyInterest == 0 ){
      return "";
    }else if(monthlyInterest==0 && term != 0){
      return "Sin Intereses";
    }else{
      return "*Con el "+monthlyInterest+"% mensual s.s.i.";
    }
  }
  /**
   * Obtiene todos los planes para los desarrollos
   * @throws Exception Si ocurre un error en la base de datos
   * @return Object[] Lista de objetos planes venta
   */
  public static Object[] getData( )throws Exception{
    ArrayList salesPlans = new ArrayList();
    Driver DriverRs = (Driver)Class.forName(driver).newInstance();
    Connection ConnRs = DriverManager.getConnection(strconn, username,password);
    Statement stmt_plan = ConnRs.createStatement();
    String str_plan =
        "select  iddesarrollo as cvedesarrollo, "+
        "        NoPlan, " +
        "        0  as Factor, "+
        "        DesctoContado as Contado,  " +
        "        PorcEnganche as Enganche, " +
        "        Plazo, " +
        "        IntMensual, " +
        "        GastosAdmon, " +
        "        Seguro " +
        " from 	 planesfinanciamiento ";
    ResultSet rs_plan = stmt_plan.executeQuery(str_plan);
    while( rs_plan.next() ){
      salesPlans.add( SalesPlan.getSalesPlan( rs_plan ) );
    }
    ConnRs.close();
    return salesPlans.toArray();
  }
  /**
   * Gets Sales plan of this development
   * @param developmentCode Development Code
   * @return Sales plans
   */
  public static Object[] getData( String developmentCode )throws Exception{
    ArrayList salesPlans = new ArrayList();
    Driver DriverRs = (Driver)Class.forName(driver).newInstance();
    Connection ConnRs = DriverManager.getConnection(strconn, username,password);
    Statement stmt_plan = ConnRs.createStatement();
    String str_plan =
        "select  iddesarrollo as cvedesarrollo, "+
        "        NoPlan, " +
        "        0  as Factor, "+
        "        DesctoContado as Contado,  " +
        "        PorcEnganche as Enganche, " +
        "        Plazo, " +
        "        IntMensual, " +
        "        GastosAdmon, " +
        "        Seguro " +
        " from 	 planesfinanciamiento "+
        " where  IdDesarrollo = '"+developmentCode+"' ";
    ResultSet rs_plan = stmt_plan.executeQuery(str_plan);
    while( rs_plan.next() ){
      salesPlans.add( SalesPlan.getSalesPlan( rs_plan ) );
    }
    ConnRs.close();
    return salesPlans.toArray();
  }

  /**
   * Gets Sales plan with this number of this development
   * @param number Plan Number
   * @param developmentCode Development Code
   * @return Sales plans
   */
  public static SalesPlan getData( int number, String developmentCode )throws Exception{
	SalesPlan salesPlan = null;
    Driver DriverRs = (Driver)Class.forName(driver).newInstance();
    Connection ConnRs = DriverManager.getConnection(strconn, username,password);
    Statement stmt_plan = ConnRs.createStatement();
    String str_plan =
        "select  iddesarrollo as cvedesarrollo, "+
        "        NoPlan, " +
        "        0  as Factor, "+
        "        DesctoContado as Contado,  " +
        "        PorcEnganche as Enganche, " +
        "        Plazo, " +
        "        IntMensual, " +
        "        GastosAdmon, " +
        "        Seguro " +
        " from 	 planesfinanciamiento "+
        " where  NoPlan = "+number+" and idDesarrollo = '"+developmentCode+"'";
    ResultSet rs_plan = stmt_plan.executeQuery(str_plan);
    if( rs_plan.next() ){
    	salesPlan = SalesPlan.getSalesPlan( rs_plan );
    }
    ConnRs.close();
    return salesPlan;
  }
  /**
   * Fill and return Sales Plan from the resultSet
   * @param rs_plan ResultSet to fill Sales Plan
   * @return Sales Plan Filled
   */
  private static SalesPlan getSalesPlan( ResultSet rs_plan )throws Exception{
    SalesPlan plan = null;
    if( rs_plan != null ){
      plan = new SalesPlan( );
      plan.setDevelopmentCode( rs_plan.getString("CveDesarrollo") );
      plan.setCashPayment( rs_plan.getDouble("Contado") );
      plan.setDownPayment( rs_plan.getDouble("Enganche") );
      plan.setFactor( rs_plan.getDouble("Factor") );
      plan.setInsurance( rs_plan.getDouble("Seguro") );
      plan.setMonthlyInterest( rs_plan.getDouble("IntMensual") );
      plan.setNumber( rs_plan.getInt("NoPlan") );
      plan.setTerm( rs_plan.getInt("Plazo") );
      plan.setManagementCost( rs_plan.getDouble("GastosAdmon") );
    }
    return plan;
  }
  /**
   * Not Implement
   * @param session to save message attibute
   * @throws java.lang.Exception
   */
  public void saveData(HttpSession session) throws Exception {
    /**@todo Implement this kines.SerializableDB method*/
    throw new java.lang.UnsupportedOperationException("Method saveData() not yet implemented.");
  }
  /**
   * Not Implement
   * @return
   * @throws java.lang.Exception
   */
  public boolean deleteData() throws Exception {
    /**@todo Implement this kines.SerializableDB method*/
    throw new java.lang.UnsupportedOperationException("Method deleteData() not yet implemented.");
  }
  /**
   * Genera corrida
   * @param plazo int
   * @param interes double
   * @param gastosAdmon double
   * @param seguro double
   * @param total double
   * @param primerPago int
   * @param enganche String
   * @param engancheT double
   * @param fechaFirma String
   * @param tipoPago int
   * @param mensualidad double
   * @param descuento double
   * @return Object[]
   * @throws SQLException
   * @throws Exception
   */
  public static Object [] corridaPlanEspecial(int plazo, double interes, double gastosAdmon, double seguro, double total, int primerPago, String enganche, double engancheT, String fechaFirma, int tipoPago, double mensualidad, double descuento)throws SQLException, Exception{
    ArrayList rows = new ArrayList();
    String query = "set language english EXEC sp_corrida "+plazo+","+interes+","+gastosAdmon+","+seguro+","+total+","+primerPago+",'"+enganche+"',"+engancheT+",'"+fechaFirma+"',"+tipoPago+","+mensualidad+","+descuento;
    Driver DriverRS = (Driver)Class.forName(driver).newInstance();
    Connection ConnRs = DriverManager.getConnection(strconn, username,password);
    Statement select = ConnRs.createStatement();
   ResultSet resultado = select.executeQuery(query);
    int no_pago = 0;
    Calendar fechaPago = null;
    java.util.Date fechaTmp = null;
    double saldo = 0;
    double pago_programado = 0;
    double pago_extraordinario = 0;
    double pago_total = 0;
    double seguroR = 0;
    double gastos_admon = 0;
    double intereses = 0;
    double capital = 0;

    while(resultado.next()){
      no_pago = resultado.getInt("no_pago");
      fechaTmp = resultado.getDate("fecha_pago");
      if(fechaTmp == null){
        fechaTmp = null;
      }else{
        fechaPago = Calendar.getInstance();
        fechaPago.setTime(fechaTmp);
      }
      saldo = resultado.getDouble("saldo");
      pago_programado = resultado.getDouble("pago_programado");
      pago_extraordinario = resultado.getDouble("pago_extraordinario");
      pago_total = resultado.getDouble("pago_total");
      seguroR = resultado.getDouble("seguro");
      gastos_admon = resultado.getDouble("gastos_admon");
      intereses = resultado.getDouble("intereses");
      capital = resultado.getDouble("capital");

      Object [] row = {new Integer(no_pago), fechaPago, new Double(saldo), new Double(pago_programado),
          new Double(pago_extraordinario), new Double(pago_total), new Double(seguroR),
          new Double(gastos_admon), new Double(intereses), new Double(capital)
      };
      rows.add(row);
    }
    select.close();
    ConnRs.close();
    return rows.toArray();


  }
}
